#!/usr/bin/python
# -*- coding: utf-8 -*-

###
###  This script downloads data from the moa mysql database, then parses it into sep pt, line, and poly files
###

###  DOWNLOAD THE DATA

import MySQLdb

## Open mysql connection
## ## see http://www.mikusa.com/python-mysql-docs/index.html
db = MySQLdb.connect(host="xxxxxxxxx", user="xxxxxxxxx", passwd="xxxxxxxxx", db="xxxxxxxxx")
cur = db.cursor()

## Execute the query
## ## Select only the most recent row for each UUID
cur.execute("SELECT m1.geojson, m1.uuid from `inseam_data` m1 JOIN (SELECT MAX(datetime) as d, uuid FROM `inseam_data` GROUP BY uuid) m2 on m1.uuid = m2.uuid and m1.datetime = m2.d")
rows = cur.fetchall()

## This returns a 'tuple' with each cell as a string
## ## parts of the tuple can be referred to using 'row' and 'col'
## ## eg: for row in rows:   or for col in rows: 
## ## or referred to using eg rows[0] (row 1) or rows[0][1] (row 1, col 2)

# Close cursors and databases
cur.close()
db.close()



###  PARSE THE DATA

import json
from pprint import pprint

## Blank files to write output into
pt = open('/path/to/parsed_points_YYYYMMDD_HHMM.geojson', 'w')
li = open('/path/to/parsed_lines_YYYYMMDD_HHMM.geojson', 'w')
po = open('/path/to/parsed_polys_YYYYMMDD_HHMM.geojson', 'w')

## Some opening data
pt.write('''{ "type": "FeatureCollection", "features": [''')
li.write('''{ "type": "FeatureCollection", "features": [''')
po.write('''{ "type": "FeatureCollection", "features": [''')

## Loop through each row and parse
for row in rows:
  page = row[0]
  
  ## load the data as a json object
  data = json.loads(page)
  
  ## Print a nicely formatted json object to look at
  #pprint(data)
  
  for i in range(0, len(data["Point"]["features"])):
    pt.write(json.dumps(data["Point"]["features"][i]) + ", ")
  
  for i in range(0, len(data["LineString"]["features"])):
    li.write(json.dumps(data["LineString"]["features"][i]) + ", ")
  
  for i in range(0, len(data["Polygon"]["features"])):
    po.write(json.dumps(data["Polygon"]["features"][i]) + ", ")

## Write closing brackets
pt.write('] }')
li.write('] }')
po.write('] }')

## Close the output file
pt.close
li.close
po.close




## Debugging

#import json
#fname = '/home/woodsp/Desktop/parsed_points_YYYYMMDD_HHMM.geojson'
#asd = open(fname, 'r').read()  #.lower()
#wer = json.loads(asd)
#pprint(wer)
#asd.close()


